
from openpyxl import load_workbook
from copy import copy
# 将带有合并单元格的内容打散

 

wb = load_workbook(filename="test1.xlsx")
# print(wb["A1"])

sheet_names = wb.sheetnames
print(sheet_names)
s1 = wb["Sheet1"]
s2 = wb["Sheet2"]

def deep_copy_cell(source_cell, target_cell):
    target_cell.data_type = copy(source_cell.data_type)
    target_cell.fill = copy(source_cell.fill)
    if source_cell.has_style:
        target_cell._style = copy(source_cell._style)
        target_cell.font = copy(source_cell.font)
        target_cell.border = copy(source_cell.border)
        target_cell.number_format = copy(source_cell.number_format)
        target_cell.protection = copy(source_cell.protection)
        target_cell.alignment = copy(source_cell.alignment)
    if source_cell.hyperlink:
        target_cell.hyperlink = copy(source_cell.hyperlink)
    if source_cell.comment:
        target_cell.comment = copy(source_cell.comment)

def unmerged_sheet(sheet):
    merge_cell_ranges = copy(sheet.merged_cells.ranges)
    for merge_cell in merge_cell_ranges:
        min_col = merge_cell.min_col
        min_row = merge_cell.min_row
        max_col = merge_cell.max_col
        max_row = merge_cell.max_row
        merge_cell_template = None
        merge_cell_value = None
        sheet.unmerge_cells(str(merge_cell))
        for index_col in range(min_col, max_col + 1):
            for index_row in range(min_row, max_row + 1):
                cell = sheet.cell(row=index_row, column=index_col)
                if cell.value is not None:
                    print(cell.value)
                    merge_cell_value = cell.value
                    merge_cell_template = cell
                else:
                    sheet.cell(row=index_row, column=index_col).value = merge_cell_value
                    deep_copy_cell(merge_cell_template, sheet.cell(row=index_row,column=index_col))




# wb.save("test2.xlsx")

unmerged_sheet(s1)
unmerged_sheet(s2)
wb.save("test2.xlsx")